import pandas as pd
import time
import re
import easyocr # type: ignore
from selenium import webdriver
from selenium.webdriver.chrome.service import Service
from selenium.webdriver.chrome.options import Options
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
import cv2
import numpy as np
import warnings
warnings.filterwarnings('ignore')

def preprocess_captcha(image_path):
    img = cv2.imread(image_path, 0) 
    _, binary = cv2.threshold(img, 150, 255, cv2.THRESH_BINARY_INV)  

    kernel = cv2.getStructuringElement(cv2.MORPH_RECT, (3, 3))
    processed = cv2.morphologyEx(binary, cv2.MORPH_OPEN, kernel)

    lines = cv2.HoughLinesP(processed, 1, np.pi/180, threshold=100, minLineLength=100, maxLineGap=10)
    if lines is not None:
        for line in lines:
            x1, y1, x2, y2 = line[0]
            cv2.line(img, (x1, y1), (x2, y2), (255, 255, 255), 2) 

    cv2.imwrite('processed_image.png', img)  


def load_and_prepare_data(file_path):
    df = pd.read_excel(file_path, engine='openpyxl', skiprows=1)
    df.rename(columns={'Unnamed: 0': '日期'}, inplace=True)
    df['日期'] = pd.to_datetime(df['日期'])
    df.fillna(0, inplace=True)
    df.sort_values(by='日期', ascending=True, inplace=True)
    if '零星废水储量(吨)' in df.columns:
        df['产生数量'] = df['零星废水储量(吨)'].diff(periods=1).apply(lambda x: 0 if x < 0 else x).round(2)
        df['产生数量'].fillna(0, inplace=True)
    else:
        print("Error: '零星废水储量(吨)' column not found.")
    df.sort_values(by='日期', ascending=False, inplace=True)
    # print(df.head(40))
    # print('*'*10)
    return df

def setup_selenium(driver_path):
    service = Service(driver_path)
    options = Options()
    options.add_argument('--ignore-ssl-errors=yes')
    options.add_argument('--ignore-certificate-errors')
    driver = webdriver.Chrome(service=service, options=options)
    return driver

def navigate_and_login(driver, url, username, password):
    driver.get(url)
    wait = WebDriverWait(driver, 10)
    
    login_button = wait.until(EC.presence_of_element_located((By.ID, 'btnlogin')))
    driver.find_element(By.ID, 'username').send_keys(username)
    driver.find_element(By.ID, 'password').send_keys(password)
    reader = easyocr.Reader(['en']) 
    max_attempts = 5
    for attempts in range(max_attempts):
        captcha_element = WebDriverWait(driver, 10).until(EC.presence_of_element_located((By.ID, "login_verifycode")))
        captcha_element.screenshot('captcha.png')
        preprocess_captcha('captcha.png')
        captcha_text = reader.readtext('captcha.png')[0][-2].strip()
        filtered_text = re.sub("[^0-9]", "", captcha_text)
        cleaned_text = filtered_text.replace(" ", "")
        driver.find_element(By.ID, 'username').clear()
        driver.find_element(By.ID, 'username').send_keys(username)
        driver.find_element(By.ID, 'password').clear()
        driver.find_element(By.ID, 'password').send_keys(password)
        driver.find_element(By.NAME, 'verifycode').clear()
        driver.find_element(By.NAME, 'verifycode').send_keys(cleaned_text)
        time.sleep(3)
        driver.find_element(By.ID, 'btnlogin').click()

        try:
            WebDriverWait(driver, 3).until(EC.visibility_of_element_located((By.CSS_SELECTOR, ".login_tips")))
            error_message = driver.find_element(By.CSS_SELECTOR, ".login_tips").text
            if "验证码错误" in error_message:
                print("Verify code is error, try again...")
                time.sleep(1)
                driver.refresh()  
                # continue
            else:
                print("登录成功！")
                # mflag = 1
                wait.until(EC.presence_of_element_located((By.NAME, 'viewport')))
                return True
        except Exception as e:
            # print(f"error:{e}")
            return True
    print("已达到最大尝试次数，未能成功登录。")
    return False
    
    

def manually_operated(driver, url, username, password):
    driver.get(url)
    wait = WebDriverWait(driver, 10)
    login_button = wait.until(EC.presence_of_element_located((By.ID, 'btnlogin')))
    driver.find_element(By.ID, 'username').send_keys(username)
    driver.find_element(By.ID, 'password').send_keys(password)
    captcha_code = input("Please enter the captcha code shown: ")
    driver.find_element(By.NAME, "verifycode").send_keys(captcha_code)
    driver.find_element(By.ID, "btnlogin").click()
    if wait.until(EC.presence_of_element_located((By.NAME, 'viewport'))):
        return True
    else:
        return False

def fill_and_submit_forms(driver, df):
    wait = WebDriverWait(driver, 10)
    internal_ledger = wait.until(EC.element_to_be_clickable((By.CSS_SELECTOR, "#sidebar-menu > li:nth-child(1)")))
    internal_ledger.click()
    internal_ledger_link = wait.until(EC.element_to_be_clickable((By.CSS_SELECTOR, "#sidebar-menu > li.treeview.active > ul > li:nth-child(9) > a")))
    internal_ledger_link.click()

    for index, row in df.iterrows():
        total_rows = len(df)
        # print(f'index:{index},row:{row}')
        if index < total_rows - 1:
            driver.switch_to.default_content()
            iframe = wait.until(EC.presence_of_element_located((By.NAME, 'iframebeaebdb2-5519-4163-9002-ded03e6b31a9')))  # 数据页面
            driver.switch_to.frame(iframe)
            element = driver.find_element(By.ID, "lr-add")
            driver.execute_script("arguments[0].click();", element)
            time.sleep(2)
            
            driver.switch_to.default_content()
            iframe0 = wait.until(EC.presence_of_element_located((By.NAME, 'WasteProduceForm')))   # 新增页面
            driver.switch_to.frame(iframe0)  
            
            FillDate_input = driver.find_element(By.ID, 'FillDate')  # 日期
            WaterLoss_input = driver.find_element(By.ID, 'WaterLoss') # 生产性日用水量
            ProduceDate_input = driver.find_element(By.ID, 'ProduceDate') # 生产日期
            Process_input = driver.find_element(By.ID, 'Process') # 产生工序
            WasteNum_input = driver.find_element(By.ID, 'WasteNum') # 产生数量
            StandingStock_input = driver.find_element(By.ID, 'StandingStock') # 收集容器现存量
            
            driver.execute_script("document.getElementById('FillDate').onfocus = function() {};")
            FillDate_input.clear()
            date=str(row['日期'])
            driver.execute_script("document.getElementById('FillDate').value = arguments[0];", date)
            # print(f'日期：{date}')

            WaterLoss_input.clear()
            WaterLoss_input.send_keys(str(row['自来水工业用水流量(立方米)']))
            # liul=str(row['自来水工业用水流量(立方米)'])
            # print(f'生产性日用水量：{liul}')

            driver.execute_script("document.getElementById('ProduceDate').onfocus = function() {};")
            ProduceDate_input.clear()
            driver.execute_script("document.getElementById('ProduceDate').value = arguments[0];", date)

            Process_input.clear()
            Process_input.send_keys('清洗')

            WasteNum_input.clear()
            WasteNum_input.send_keys(str(row['产生数量']))
            # num=str(row['产生数量'])
            # print(f'产生数量：{num}')

            StandingStock_input.clear()
            StandingStock_input.send_keys(str(row['零星废水储量(吨)']))

            select_button = wait.until(EC.element_to_be_clickable((By.CSS_SELECTOR, "#wp_form > tbody > tr:nth-child(7) > td.formValue > span")))
            select_button.click()   # 点击选择废水种类请选择
        
            #-----------------选择废水种类--------------
            driver.switch_to.default_content()
            iframe1 = WebDriverWait(driver, 10).until(EC.presence_of_element_located((By.ID, "EnterpriseWasteWaterListWin")))
            driver.switch_to.frame(iframe1)
            row_AB004 = wait.until(EC.element_to_be_clickable((By.ID, '1'))) # //*[@id="1"]
            row_AB004.click()  # 选择这一行   
            yes_button = driver.find_element(By.ID, 'lr-replace')
            yes_button.click()   # 确认
            # print("选择框1  ok!!!!!")


            #-------------------新增--------------
            driver.switch_to.frame(iframe0)
            select_button = wait.until(EC.element_to_be_clickable((By.CSS_SELECTOR, "#wp_form > tbody > tr:nth-child(10) > td.formValue > span"))) 
            select_button.click()   # 点击收集容器材质请选择
            
            #-----------------收集容器材质--------------
            driver.switch_to.default_content()
            iframe2 = WebDriverWait(driver, 10).until(EC.presence_of_element_located((By.ID, "EnterpriseWasteStorageListWin")))
            driver.switch_to.frame(iframe2)
            row_AB004 = wait.until(EC.element_to_be_clickable((By.ID, '1'))) # //*[@id="1"]
            row_AB004.click()  # 选择这一行   
            yes_button = driver.find_element(By.ID, 'lr-replace')
            yes_button.click()   # 确认
            # print("选择框2  ok!!!!!")
            time.sleep(3)

            driver.switch_to.default_content()
            submit_button = wait.until(EC.element_to_be_clickable((By.CLASS_NAME, "layui-layer-btn0")))   # layui-layer-btn1是关闭按钮 layui-layer-btn0是确认按钮
            submit_button.click()

        if index == total_rows - 1:
            print("到达最后一行，不执行提交操作")
        else:
            print(f"已处理并提交{date}")
        time.sleep(6)

def main():
    excel_path = r'C:\Users\Umbra\Desktop\1.xlsx'    # excel文件路径
    driver_path = r'E:\C_software\Application\chromedriver-win64\chromedriver.exe'   # chromedriver.exe驱动路径
    site_url = 'http://lsfs.wzy-i.com/'  
    username = 'iww21061888'  # 用户名
    password = 'Ab1234567+'  # 密码
    df = load_and_prepare_data(excel_path)
    driver = setup_selenium(driver_path)
    try:
        flag = navigate_and_login(driver, site_url, username=username, password=password)
        if not flag:
            manually_operated(driver, site_url, username=username, password=password)
        fill_and_submit_forms(driver, df)
    finally:
        driver.quit()

if __name__ == "__main__":
    main()
